-- @owner: opentestcase016
-- @date: 2022-10-24
-- @testpoint: string测试,测试含all,having,group by,order by

--step1:创建表并插入数据 expect:成功	
drop table if exists t_ustore_aggfunc_0023;
create table t_ustore_aggfunc_0023
(
 d_street_1 varchar(20),
 d_city varchar(20),
 d_zip char(20) default 'djhaksdqd22d',
 d_id integer,
 d_timestamptz timestamp with time zone,
 d_timestamp timestamp without time zone,
 d_interval varchar(20) default '2days 03:30:00',
 d_decimal decimal(10) default 988082,
 d_text text not null,
 d_numeric numeric,
 d_date date default '2015-02-15') with (storage_type=ustore);

create index i_ustore_aggfunc_0022_01 on t_ustore_aggfunc_0023(d_street_1,d_interval) ;
create index i_ustore_aggfunc_0022_02 on t_ustore_aggfunc_0023(d_text);
create index i_ustore_aggfunc_0022_03 on t_ustore_aggfunc_0023(d_date) ;

--step2:创建序列,开启匿名块 expect:成功
drop sequence if exists s_ustore_aggfunc_0023;
create sequence s_ustore_aggfunc_0023 start with 1 maxvalue 500 increment by 1 cycle;
declare
x number;
begin
x := 0;
loop
  insert into t_ustore_aggfunc_0023 (d_street_1,d_city,d_zip,d_id,d_timestamptz,d_timestamp,d_text,d_numeric) values ('autyfiueifjol','halaimazi','lanilolia',s_ustore_aggfunc_0023.nextval,'2014-04-24 22:50:31.000000 +8:00','2014-04-30 22:15:31','222utuygtu',1245.33);
  x := x + 1;
  exit when x > 499;
end loop;
end;
/

--step3:string测试,测试含all,having,group by,order by;expect:成功
select concat_ws('|', t3.d_text),length(substr(concat_ws('|', t3.d_zip),6)),replace(t3.d_zip,'g','k'),avg(t3.d_numeric+2)
from t_ustore_aggfunc_0023 t3
where t3.d_timestamptz='2014-04-24 22:50:31.000000 +08:00'
and length(concat(t3.d_zip,t3.d_numeric))>=t3.d_id/2
or t3.d_street_1=' '
group by concat_ws('|', t3.d_text),length(substr(concat_ws('|', t3.d_zip),6)),replace(t3.d_zip,'g','k')
having length(substr(concat_ws('|', t3.d_zip),6))<=20
and concat_ws('|', t3.d_text)='222utuygtu'
order by concat_ws('|', t3.d_text),2,3,4;

--step4:清理环境 expect:成功
drop sequence if exists s_ustore_aggfunc_0023;
drop table if exists t_ustore_aggfunc_0023;
